Banco de Dados Sistema Escolar

Aqui temos um banco de dados para armazenar informações de um sistema escolar. Neste modelo é possível gerenciar alunos, professores, cursos, disciplinas, turmas e matrículas.

Visão Geral do Modelo

O sistema é composto basicamente por seis grandes áreas:

Alunos
Professores
Cursos
Turmas
Disciplinas
Matrícula

Entidades (tabelas) e Atributos (colunas)

PESSOA

id_pessoa (PK)
nome
data_nascimento
sexo

Obs: Entidade genérica que representa indivíduos do sistema.

ALUNO

id_aluno (PK)
id_pessoa (FK, UNIQUE)

Obs: Especialização de Pessoa.

PROFESSOR

id_professor (PK)
formacao
id_pessoa (FK, UNIQUE)

Obs: Especialização de Pessoa.

EMAIL

id_email (PK)
email
id_pessoa (FK)

Obs: Uma pessoa pode possuir vários e-mails.

TELEFONE

id_telefone (PK)
telefone
id_pessoa (FK)

Obs: Uma pessoa pode possuir vários telefones.

CURSO

id_curso (PK)
nome
descricao
data_inicio
data_fim

DISCIPLINA

id_disciplina (PK)
nome
carga_horaria
data_inicio
data_fim
id_curso (FK)
id_professor (FK)

TURMA

id_turma (PK)
nome
ano
semestre
id_curso (FK)

MATRÍCULA

id_matricula (PK)
data_matricula
status
id_aluno (FK)
id_turma (FK)

Relacionamentos e Cardinalidades

PESSOA (1,N) — EMAIL

Uma pessoa pode ter vários e-mails.
PESSOA (1,N) — TELEFONE

Uma pessoa pode ter vários telefones.
PESSOA (1,1) — ALUNO (especialização)

Um aluno é uma pessoa.
PESSOA (1,1) — PROFESSOR (especialização)

Um professor é uma pessoa.
CURSO (1,N) — DISCIPLINA

Um curso é composto por uma ou mais disciplinas.
PROFESSOR (1,N) — DISCIPLINA

Um professor pode ministrar várias disciplinas.
CURSO (1,N) — TURMA

Um curso pode ter várias turmas.
ALUNO (1,N) — MATRÍCULA — (1,N) TURMA

Relacionamento muitos-para-muitos entre aluno e turma.

Diagrama Entidade Relacionamento

Script de Criação do Banco de Dados

DROP DATABASE IF EXISTS sistema_escolar;
CREATE DATABASE sistema_escolar;
USE sistema_escolar;

CREATE TABLE pessoa (
    id_pessoa INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    data_nascimento DATE,
    sexo CHAR(1)
);

CREATE TABLE email (
    id_email INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL,
    id_pessoa INT NOT NULL,
    CONSTRAINT fk_email_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE telefone (
    id_telefone INT AUTO_INCREMENT PRIMARY KEY,
    telefone VARCHAR(20) NOT NULL,
    id_pessoa INT NOT NULL,
    CONSTRAINT fk_telefone_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE aluno (
    id_aluno INT AUTO_INCREMENT PRIMARY KEY,
    id_pessoa INT NOT NULL UNIQUE,
    CONSTRAINT fk_aluno_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE professor (
    id_professor INT AUTO_INCREMENT PRIMARY KEY,
    formacao VARCHAR(100),
    id_pessoa INT NOT NULL UNIQUE,
    CONSTRAINT fk_professor_pessoa
        FOREIGN KEY (id_pessoa) REFERENCES pessoa(id_pessoa)
);

CREATE TABLE curso (
    id_curso INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao VARCHAR(255),
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL
);

CREATE TABLE disciplina (
    id_disciplina INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    carga_horaria INT NOT NULL,
    data_inicio DATE NOT NULL,
    data_fim DATE NOT NULL,
    id_curso INT NOT NULL,
    id_professor INT NOT NULL,
    CONSTRAINT fk_disciplina_curso
        FOREIGN KEY (id_curso) REFERENCES curso(id_curso),
    CONSTRAINT fk_disciplina_professor
        FOREIGN KEY (id_professor) REFERENCES professor(id_professor)
);

CREATE TABLE turma (
    id_turma INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    ano INT NOT NULL,
    semestre INT NOT NULL,
    id_curso INT NOT NULL,
    CONSTRAINT fk_turma_curso
        FOREIGN KEY (id_curso) REFERENCES curso(id_curso)
);


CREATE TABLE matricula (
    id_matricula INT AUTO_INCREMENT PRIMARY KEY,
    data_matricula DATE NOT NULL,
    status VARCHAR(20),
    id_aluno INT NOT NULL,
    id_turma INT NOT NULL,
    CONSTRAINT fk_matricula_aluno
        FOREIGN KEY (id_aluno) REFERENCES aluno(id_aluno),
    CONSTRAINT fk_matricula_turma
        FOREIGN KEY (id_turma) REFERENCES turma(id_turma)
);

INSERT INTO pessoa (nome, data_nascimento, sexo) VALUES
('João Pereira', '2002-05-10', 'M'),
('Maria Oliveira', '2001-08-22', 'F'),
('Pedro Santos', '2003-03-15', 'M'),
('Carlos Silva', '1980-04-12', 'M'),
('Ana Souza', '1985-09-30', 'F');

INSERT INTO email (email, id_pessoa) VALUES
('joao@email.com', 1),
('maria@email.com', 2),
('pedro@email.com', 3),
('carlos@escola.com', 4),
('ana@escola.com', 5);

INSERT INTO telefone (telefone, id_pessoa) VALUES
('11988880001', 1),
('11988880002', 2),
('11988880003', 3),
('11999990001', 4),
('11999990002', 5);

INSERT INTO aluno (id_pessoa) VALUES
(1),
(2),
(3);

INSERT INTO professor (formacao, id_pessoa) VALUES
('Matemática', 4),
('Computação', 5);

INSERT INTO curso (nome, descricao, data_inicio, data_fim) VALUES
('Sistemas de Informação', 'Curso de tecnologia', '2024-02-01', '2027-12-15');

INSERT INTO disciplina (nome, carga_horaria, data_inicio, data_fim, id_curso, id_professor) VALUES
('Algoritmos', 80, '2024-02-15', '2024-06-30', 1, 2),
('Banco de Dados', 80, '2024-08-01', '2024-12-15', 1, 2);

INSERT INTO turma (nome, ano, semestre, id_curso) VALUES
('SI-2024-1', 2024, 1, 1);

INSERT INTO matricula (data_matricula, status, id_aluno, id_turma) VALUES
('2024-02-10', 'ATIVA', 1, 1),
('2024-02-10', 'ATIVA', 2, 1);

Exercícios de SQL – Sistema Escolar

NÍVEL 1 – Consultas Básicas (SELECT)

Liste todos os alunos, exibindo apenas o nome e o sexo.
Liste todos os professores e suas formações.
Exiba todos os cursos com suas datas de início e fim.
Liste todas as disciplinas cadastradas.
Liste todas as turmas do ano de 2024.

NÍVEL 2 – SELECT com WHERE e ORDER BY

Liste os alunos do sexo feminino.
Liste os professores com formação em Computação.
Liste as disciplinas com carga horária maior ou igual a 80 horas.
Exiba as turmas do 1º semestre, ordenadas pelo nome.
Liste os cursos que terminam após o ano de 2026.

NÍVEL 3 – JOIN (Relacionamentos)

Liste o nome do aluno e o nome da turma em que ele está matriculado.
Liste o nome do aluno e o nome do curso que ele está cursando.
Liste o nome do professor e as disciplinas que ele ministra.
Liste as disciplinas junto com o nome do curso ao qual pertencem.
Liste o nome da turma e o nome do curso correspondente.

NÍVEL 4 – JOIN com EMAIL e TELEFONE

Liste o nome do aluno e seus e-mails.
Liste o nome do professor e seus telefones.
Liste todas as pessoas com seus respectivos e-mails.
Liste os alunos que possuem mais de um telefone.
Liste as pessoas que não possuem e-mail cadastrado.

NÍVEL 5 – Funções de Agregação e GROUP BY

Quantos alunos estão matriculados no sistema?
Quantas disciplinas existem por curso?
Quantos alunos existem em cada turma?
Exiba o total de disciplinas ministradas por cada professor.
Exiba o ano mais recente de início dos cursos.

NÍVEL 6 – Subconsultas

Liste os alunos matriculados na turma "SI-2024-1".
Liste os professores que não ministram nenhuma disciplina.
Liste os cursos que possuem mais de uma disciplina.
Liste os alunos que não estão matriculados em nenhuma turma.
Liste as disciplinas cujo período está dentro do intervalo do curso.

NÍVEL 7 – Desafios (Integração Completa)

Liste o nome do aluno, e-mail e telefone.
Liste o nome do professor, sua formação e as disciplinas que ministra.
Liste o nome do curso, suas disciplinas e o professor responsável.
Liste os alunos, as turmas e os cursos, ordenados pelo nome do aluno.
Gere um relatório com:

Nome do curso
Quantidade de turmas
Quantidade de disciplinas